package co.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;



import co.com.util.ConexionDirecta;
import co.com.vo.CarreraVO;
import co.com.vo.ParticipanteVO;
import co.com.vo.TipoCarreraVO;

public class CarreraDAO {
	
	private String SQL_INSERT = "INSERT INTO CARRERA (ID,TIPOCARRERA,FECHA,DURACION,NOMBRE,CANTVUELTAS) VALUES (?,?,?,?,?,?)";
	private String SQL_INSERT_PARTICIPANTE_CARRERA = "INSERT INTO PARTICIPANTE_CARRERA (IDCARRERA,IDPARTICIPANTE) VALUES (?,?)";
	private String SQL_UPDATE = "UPDATE CARRERA SET TIPOCARRERA=?, FECHA=?, DURACION=?, NOMBRE=?, CANTVUELTAS=? WHERE ID=?";
	private String SQL_DELETE = "DELETE CARRERA WHERE ID=?";
	private String SQL_SELECT = "SELECT ID,TIPOCARRERA,FECHA,DURACION,NOMBRE,CANTVUELTAS FROM CARRERA";
	TipoCarreraVO tipoCarreraVO=new TipoCarreraVO();
	
	
	public boolean insertCarrera(CarreraVO carreraVO) throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_INSERT);
			pt.setInt(1, carreraVO.getId());
			pt.setInt(2, carreraVO.getTipoCarrera().getId());
			pt.setString(3, carreraVO.getFecha());
			pt.setInt(4, carreraVO.getDuracion());
			pt.setString(5, carreraVO.getNombre());
			pt.setInt(6, carreraVO.getCantVueltas());
			
			pt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
		}
		
		return false;
	}
	
	public boolean insertParticipanteCarrera(CarreraVO carreraVO, ParticipanteVO participanteVO) throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_INSERT_PARTICIPANTE_CARRERA);
			pt.setInt(1, carreraVO.getId());
			pt.setInt(2, participanteVO.getId());
			
			pt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
		}
		
		return false;
	}
	
	public boolean updateCarrera(CarreraVO carreraVO) throws SQLException{
		
		Connection conn =null;
		PreparedStatement pt=null;
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_UPDATE);
			
			pt.setInt(1, carreraVO.getTipoCarrera().getId());
			pt.setString(2, carreraVO.getFecha());
			pt.setInt(3, carreraVO.getDuracion());
			pt.setString(4, carreraVO.getNombre());
			pt.setInt(5, carreraVO.getCantVueltas());
			pt.setInt(6, carreraVO.getId());
			
			pt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
		}
		
		return false;
		
	}
	
	public boolean deleteCarrera(CarreraVO carreraVO) throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_DELETE);
			pt.setInt(1, carreraVO.getId());
			
			
			pt.executeUpdate();
			
			return true;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
		}
		
		return false;
	}
	
	public List<CarreraVO> selectCarrera(CarreraVO carreraVO) throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		ResultSet rs = null;
		List<CarreraVO> lista = new ArrayList<CarreraVO>();
		try {
			
			if(carreraVO == null){
				return null;
			}
			
			StringBuffer st = new StringBuffer();
			st.append(SQL_SELECT+" WHERE ");
			
			boolean flag=false;
			
			if(carreraVO.getId()!=null){
				st.append("ID="+carreraVO.getId());
				flag=true;
			}
			
			if( carreraVO.getTipoCarrera().getId()!=null){
				if(flag){
					st.append(",");
				}
				st.append("TIPOCARRERA="+carreraVO.getTipoCarrera());
				flag=true;
			}
			
			if(carreraVO.getFecha()!=null){
				if(flag){
					st.append(",");
				}
				SimpleDateFormat sd = new SimpleDateFormat("dd-MM-yyyy HH:mm:ss");
				st.append("to_char(FECHA,'DD-MM-YYYY HH24:MI:SS')="+sd.format(carreraVO.getFecha()));
				flag=true;
			}
			
			if(carreraVO.getDuracion()!=null){
				if(flag){
					st.append(",");
				}
				st.append("DURACION="+carreraVO.getDuracion());
				flag=true;
			}
			
			if(carreraVO.getNombre()!=null && !carreraVO.getNombre().equals("")){
				if(flag){
					st.append(",");
				}
				st.append("NOMBRE="+carreraVO.getNombre());
				flag=true;
			}
			
			if(carreraVO.getCantVueltas()!=null){
				if(flag){
					st.append(",");
				}
				st.append("CANTVUELTAS="+carreraVO.getCantVueltas());
				flag=true;
			}
			

			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(st.toString());
			
			rs=pt.executeQuery();
			TipoCarreraDAO tipoDao = new TipoCarreraDAO();

			while(rs.next()){
				CarreraVO aux=new CarreraVO();
				aux.setId(rs.getInt(1));
				
				TipoCarreraVO tipo = new TipoCarreraVO();
				tipo.setId(rs.getInt(2));
				TipoCarreraDAO tipoCarreraDAO = new TipoCarreraDAO();
				List<TipoCarreraVO> result=tipoCarreraDAO.select(tipo);
				if(result!=null && !result.isEmpty()){
					aux.setTipoCarrera(result.get(0));
				}
				aux.setFecha(rs.getString(3));
				aux.setDuracion(rs.getInt(4));
				aux.setNombre(rs.getString(5));
				aux.setCantVueltas(rs.getInt(6));

				lista.add(aux);
			}
			
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
			if(rs!=null){
				rs.close();
			}
		}
		
		return null;	
	}
	
	public List<CarreraVO> selectAllCarrera() throws SQLException{
		Connection conn =null;
		PreparedStatement pt=null;
		ResultSet rs = null;
		List<CarreraVO> lista = new ArrayList<CarreraVO>();
		try {
			conn = ConexionDirecta.getOracleConnection();
			pt = conn.prepareStatement(SQL_SELECT);
			
			rs=pt.executeQuery();
			TipoCarreraDAO tipoDao = new TipoCarreraDAO();

			while(rs.next()){
				CarreraVO aux=new CarreraVO();
				aux.setId(rs.getInt(1));
				TipoCarreraVO tipo = new TipoCarreraVO();
				tipo.setId(rs.getInt(2));
				TipoCarreraDAO tipoCarreraDAO = new TipoCarreraDAO();
				List<TipoCarreraVO> result=tipoCarreraDAO.select(tipo);
				if(result!=null && !result.isEmpty()){
					aux.setTipoCarrera(result.get(0));
				}		
				aux.setFecha(rs.getString(3));
				aux.setDuracion(rs.getInt(4));
				aux.setNombre(rs.getString(5));
				aux.setCantVueltas(rs.getInt(6));

				lista.add(aux);
			}
			
			return lista;
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(conn!=null){
				conn.close();
			}
			if(pt!=null){
				pt.close();
			}
			if(rs!=null){
				rs.close();
			}
		}
		
		return null;
	}

}
